﻿using System;
using System.Collections.Generic;

using System.Web;
using System.Configuration;
using System.Data.SqlClient;

/// <summary>
/// Summary description for D_PurchaseOrder
/// </summary>
namespace Bestlife.ERP.DataAccess
{
 
    public class D_PurchaseOrder
    {
        public static Boolean status1 = false;
        public static Boolean status2 = false;
        public static int proid;
        public static List<string> productid = new List<string>();
        public static decimal price;
        public static decimal retprice;
        public static string count;
        public static int lastid;
        public static int pid;
        public static int pid1;
        public static Boolean result=false;
        public static Boolean result1 = false;
    public static Boolean status = false;
    public static int Code;

        public D_PurchaseOrder()
        {
            //
            // TODO: Add constructor logic here
            //
        }

         public static int lastGRNid()
            {
            SqlDataReader rdR;
            try
            {
               
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT MAX(Or_ID) as lastid  FROM [Tbl_GRN]";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    lastid=(int)rdR["lastid"]+1; 
                    
                }
                rdR.Close();
            }
            catch
            {
            }
             return lastid;          

        }
    
        public static Bestlife.ERP.Entities.E_PurchaseOrder getprice(string pro)
        {
            Bestlife.ERP.Entities.E_PurchaseOrder po = new Entities.E_PurchaseOrder();
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

                String Ssql = "SELECT [Pro_ID] ,[Code] FROM [Tbl_Stock]  where [Sto_ID]='" + pro + "'";
                // String Ssql = "SELECT [Ret_Price] FROM [Tbl_Product] where [Pro_ID]='" + pro + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    string proid = rdR["Pro_ID"].ToString();
                    string code = rdR["Code"].ToString();
                    po.Code = code;
                    rdR.Close();
                    String Ssql1 = "SELECT [Pur_Price],[Ret_Price]  FROM [Tbl_Product]  where Pro_ID='" + proid + "' and Code='" + code + "'";
                    rdR = Connection.ReadTable(Ssql1);

                    if (rdR.Read())
                    {
                        po.Unitvalue = (decimal)rdR["Pur_Price"];
                        po.Retprice = (decimal)rdR["Ret_Price"];
                    }
                    Connection.con.Close();

                }
                rdR.Close();
            }
            catch
            {
            }
            return po;


        }

        public static Decimal linetotal(decimal qty, decimal unitprice)
        {
            decimal line_total = qty * unitprice;
            Connection.con.Close();
            return line_total;
        }

        public static Boolean insert(Bestlife.ERP.Entities.E_PurchaseOrder order, string user)
        {
            try
            {
              int stoid=Bestlife.ERP.DataAccess.D_PurchaseOrder.getcode(order.Stockid);
                DateTime date = System.DateTime.Now;
              
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                string result = getproname(order.Proname);
                string sql = "INSERT INTO [Tbl_GRNrequest]([Or_ID],[Pro_Name],[Pur_Price],[Description],[Qty],[Date_Create],[User_Create],[Pro_ID],[Sto_ID],[Code]) VALUES ('" + order.Or_ID1 + "','" + result + "','" + order.Unitvalue + "','" + order.Description + "','" + order.Qty + "','" + date + "','" + user + "','" + order.Proname + "','" + order.Stockid + "','" + stoid + "')";

                Boolean add = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);

                if (add)
                {
                    status = true;
                    Connection.con.Close();
                    return status;
                }
                Connection.con.Close();
               
            }
            catch
            {
            }
            return status;
        }

        public static string getproname(string pro)
        {
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT [Pro_Name] FROM [Tbl_Product] where [Pro_ID]='" + pro + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    string proname = rdR["Pro_Name"].ToString();
                    rdR.Close();
                    return proname;
                }
                rdR.Close();
            }
            catch
            {
            }

            string error = "error";
            return error;

        }

        public static Boolean insertorder(Bestlife.ERP.Entities.E_PurchaseOrder order)
        {
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;

                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                string sql1 = "select count(Pro_ID) as count from Tbl_GRNrequest where Or_ID='" + order.Or_ID1 + "'";
                rdR = Connection.ReadTable(sql1);

                if (rdR.Read())
                {
                    int count = (int)rdR["count"];
                    rdR.Close();
                    string sql = "INSERT INTO [Tbl_GRN]([Or_ID],[Oder_Date],[Book_GRNno],[Date_Create],[TotalProduct]) VALUES('" + order.Or_ID1 + "','" + order.Issu + "','" + order.Due + "','" + date + "','" + count + "')";
                  Boolean add=Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
                  if (add)
                  {
                      Connection.con.Close();
                      rdR.Close();
                      result = true;
                   
                  }
                  rdR.Close();
                }
                
            }
            catch
            {
                Connection.con.Close();

               
            }
            return result;
            
        }

     
        public static Boolean insertsupporder(Bestlife.ERP.Entities.E_PurchaseOrder sup, string user)
        {
            try
            {
                DateTime date = System.DateTime.Now;

                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

                string sql = "INSERT INTO [Tbl_SupplierOrder]([Sup_ID],[Sup_Name],[Or_ID],[Supplier_Bill_No],[Date_Create],[User_Create])VALUES('" + sup.Supid + "','" + sup.Supplier + "','" + sup.Or_ID1 + "','" + sup.Contrat + "','" + date + "','" + user + "')";
                Boolean add = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
                if (add)
                {
                    Connection.con.Close();
                    result1 = true;
                   
                }

            }
            catch
            {
                Connection.con.Close();
                
            }
            return result1;
        }

        public static Boolean total(Bestlife.ERP.Entities.E_PurchaseOrder tot, string user)
        {
            Boolean status = false;
            DateTime date = System.DateTime.Now;

            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
            string sql1 = "SELECT  [Total] FROM [Tbl_GRN]";
            Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql1);
            if (edit1)
            {

                status2 = true;
                
                
            }
            string sql = "UPDATE [Tbl_GRN] SET [Total] ='" + tot.Total + "',[Last_Modify] ='" + date + "',[User_Create]='" + user + "'   WHERE Or_ID='" + tot.Or_ID1 + "' ";
            Boolean edit = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
            if (edit)
            {
                status = true;
                Connection.con.Close();
                return status;
            }
            Connection.con.Close();
            return status;

        }

        public static Boolean approveorder(List<Bestlife.ERP.Entities.E_GRN> grn, string user, string approval, int itemcount)
        {
            try
            {
                SqlDataReader rdR;
                string code;
                
                DateTime date = System.DateTime.Now;

                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                foreach (Bestlife.ERP.Entities.E_GRN gn in grn)
                {
                    string sql = "UPDATE [Tbl_GRNrequest] SET [manger_status] ='" + approval + "',[Last_Modify] ='" + date + "'  WHERE  [Or_ID] ='" + gn.Order + "' and [Sto_ID]='" + gn.Stockid + "'";
               
                string sql1 = "UPDATE [Tbl_GRN] SET [status] ='" + approval + "',[Last_Modify] ='" + date + "'   WHERE Or_ID='" + gn.Order + "' ";
               
                string sql2 = "SELECT [Code],[Pro_ID] FROM [Tbl_Stock]  where [Sto_ID]='" + gn.Stockid + "'";
                Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql1);
                Boolean edit = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
                rdR = Connection.ReadTable(sql2);
                if (edit && edit1 && rdR.Read())
                {
                    code = rdR["Code"].ToString();
                            rdR.Close();


                            if (edit && edit1)
                            {
                                status = true;
                                // Connection.con.Close();

                                Bestlife.ERP.DataAccess.D_PurchaseOrder.addquantity(gn.Proname,gn.Qty,user,code);
                            }
                    return status;


                }
                }
            }
            catch
            {
            }
             return status;
        }

        public static Boolean approvePO(Bestlife.ERP.Entities.E_PurchaseOrder approvPO, string user)
        {

            Boolean status = false;
            DateTime date = System.DateTime.Now;

            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

            string sql = "UPDATE [Tbl_GRNrequest]  SET [PORapprove] ='" + approvPO.Approval + "' ,[Last_Modify] ='" + date + "'  WHERE Or_ID='" + approvPO.Or_ID1 + "'";
            Boolean edit = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
            
            if (edit )
            {
                status = true;
                return status;
            }

            return status;


        }

        public static Boolean approveorder(Bestlife.ERP.Entities.E_GRN gn, string user, string approval, int itemcount)
        {
            try
            {
                SqlDataReader rdR;
                string code;

                DateTime date = System.DateTime.Now;

                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                //foreach (Bestlife.ERP.Entities.E_GRN gn in grn)
                //{
                string sql = "UPDATE [Tbl_GRNrequest] SET [manger_status] ='" + approval + "',[Last_Modify] ='" + date + "',[User_Create]='" + user + "'  WHERE  [Or_ID] ='" + gn.Order + "' and [Sto_ID]='" + gn.Stockid + "'";

                string sql1 = "UPDATE [Tbl_GRN] SET [status] ='" + approval + "',[Last_Modify] ='" + date + "',[User_Create]='" + user + "'   WHERE Or_ID='" + gn.Order + "' ";

                string sql2 = "SELECT [Code],[Pro_ID] FROM [Tbl_Stock]  where [Sto_ID]='" + gn.Stockid + "'";
                Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql1);
                Boolean edit = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
                rdR = Connection.ReadTable(sql2);
                if (edit && edit1 && rdR.Read())
                {
                    code = rdR["Code"].ToString();
                    rdR.Close();


                    if (edit && edit1)
                    {
                        status = true;
                        // Connection.con.Close();

                        Bestlife.ERP.DataAccess.D_PurchaseOrder.addquantity(gn.Proname, gn.Qty, user, code);
                    }
                    return status;


                }
                //}
            }
            catch
            {
            }
            return status;
        }

        public static Boolean allapproved(List<Bestlife.ERP.Entities.E_GRN> grn, string user, string approve,int itemcount)
        {
            try
            {
                SqlDataReader rdR;
                string code;
                
                DateTime date = System.DateTime.Now;

                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

                foreach (Bestlife.ERP.Entities.E_GRN gn in grn)
                {

                    string sql1 = "UPDATE [Tbl_GRNrequest] SET [manger_status] ='" + approve + "',[Last_Modify] ='" + date + "',[User_Create]='"+user+"'  WHERE  [Or_ID] ='" + gn.Order + "' and [Sto_ID]='" + gn.Stockid + "'";
                    string sql = "UPDATE [Tbl_GRN] SET [status] ='" + approve + "',[Last_Modify] ='" + date + "',[User_Create]='" + user + "'   WHERE Or_ID='" + gn.Order + "' ";
                    string sql2 = "SELECT [Code],[Pro_ID] FROM [Tbl_Stock]  where [Sto_ID]='" + gn.Stockid + "'";

                    Boolean edit = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql);
                    Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(sql1);
                    rdR = Connection.ReadTable(sql2);
                    if (edit && edit1 && rdR.Read())
                    {
                       
                            code = rdR["Code"].ToString();
                            rdR.Close();


                            if (edit && edit1)
                            {
                                status1 = true;
                                //string proid = Bestlife.ERP.DataAccess.D_receivedstock.getproid(proname);
                                Bestlife.ERP.DataAccess.D_PurchaseOrder.addquantity(gn.Proname, gn.Qty, user,code);

                            }
                        
                    }
                    

                }
            }
            catch
            {
            }
            return status1;

        }

        public static Boolean addquantity(string proid,decimal qty ,string user,string code)
        {
            Boolean upd2 = false;
            try
            {

                DateTime date = System.DateTime.Now;
                Decimal QuantityinDB = Bestlife.ERP.DataAccess.D_receivedstock.ReturnStockQuantity(proid,code);

                if (qty > 0)
                {
                    Decimal QuantityToDB = QuantityinDB + Decimal.Parse(qty.ToString());
                    if (QuantityToDB >= 0)
                    {

                        string product = getproname(proid);
                        String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                        Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class
                        string updtxt2 = "INSERT INTO [Tbl_Stock_Master]([Pro_Name],[P_Qty],[User_Create],[Date_Create],[Pro_ID],[Code]) VALUES('" + product + "','" + qty + "','" + user + "','" + date + "','" + proid + "','" + code + "')";
                        //String updtxt2 = "INSERT INTO [Tbl_Stock_Master]([Pro_Name],[P_Qty],[User_Create],[Date_Create],[Pro_ID],[Code]) VALUES('" + product + "','" + qty + "','" + user + "','" + date + "','" + proname + "','" + code + "')";

                     //String updtxt2 = "UPDATE Bonus  SET  T_commision='999' WHERE User_id='001'";
                        Boolean edit1 = Bestlife.ERP.DataAccess.Connection.AddEditDel(updtxt2);
                        if (edit1)
                        {
                            upd2 = true;
                        }
                    }
                    else
                    {
                        //print we have only QuantityinDB now !!!
                    }
                }
                else
                {
                    // sory we have nt any Product 
                }
            }
            catch
            {
            }
            return upd2;
        }

        public static int laststockid()
        {
            try
            {
                SqlDataReader rdR;

                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

                String Ssql = "SELECT MAX(Sto_ID) as lastid  FROM Tbl_Stock";
                // String Ssql = "SELECT [Ret_Price] FROM [Tbl_Product] where [Pro_ID]='" + pro + "'";

                rdR = Connection.ReadTable(Ssql);
                
                while (rdR.Read())
                {

                    pid = (int)rdR["lastid"]+1;
                  


                }
                rdR.Close();
                return pid;



            }
            catch
            {

            }

            return pid;
        }

        public static List<string> stockid(string proid)
        {
            try
            {
                SqlDataReader rdR;

                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

                String Ssql = "SELECT  [Sto_ID] FROM [Tbl_Stock]  where Pro_ID='" + proid + "'";
                // String Ssql = "SELECT [Ret_Price] FROM [Tbl_Product] where [Pro_ID]='" + pro + "'";

                rdR = Connection.ReadTable(Ssql);
                productid.Clear();
                while (rdR.Read())
                {

                    string pid = rdR["Sto_ID"].ToString();
                    productid.Add(pid);


                }
                rdR.Close();
                return productid;



            }
            catch
            {

            }

            return productid;
        }

        public static Bestlife.ERP.Entities.product setalldata(string proid)
        {
            SqlDataReader rdR;
            Bestlife.ERP.Entities.product pro = new Entities.product();
            DateTime date = System.DateTime.Now;
            String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
            Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

            String Ssql = "SELECT [Pro_ID], [Brand_Name],[Cat_Name],[Sub_Cat_Name],[Cat_ID] FROM [Tbl_Product] where  Pro_ID='" + proid + "'";
            // String Ssql = "SELECT [Ret_Price] FROM [Tbl_Product] where [Pro_ID]='" + pro + "'";

            rdR = Connection.ReadTable(Ssql);

            if (rdR.Read())
            {

                pro.Brand = rdR["Brand_Name"].ToString();
                pro.Sub_Cat_Name1 = rdR["Sub_Cat_Name"].ToString();
                pro.Cat_Name1 = rdR["Cat_Name"].ToString();
                pro.Pro_ID = rdR["Pro_ID"].ToString();
                pro.Cat_ID = rdR["Cat_ID"].ToString();


            }
            rdR.Close();
            return pro;
        }

        public static decimal lastcode(string proid)
        {
            try
            {
                SqlDataReader rdR;

                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class

                String Ssql = "SELECT MAX(Code) as lastid  from [Tbl_Product] where Pro_ID='"+proid+"'";
                // String Ssql = "SELECT [Ret_Price] FROM [Tbl_Product] where [Pro_ID]='" + pro + "'";

                rdR = Connection.ReadTable(Ssql);

                while (rdR.Read())
                {

                    pid1 = (int)rdR["lastid"] + 1;



                }
                rdR.Close();
                return pid1;



            }
            catch
            {

            }

            return pid1;
        }

        public static string getproid(string pro)
        {
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT [Pro_ID] FROM [Tbl_Product] where [Pro_Name]='" + pro + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    string proid = rdR["Pro_ID"].ToString();
                    rdR.Close();
                    return proid;
                }
                rdR.Close();
            }
            catch
            {
            }

            string error = "error";
            return error;

        }

        public static int getsupid(string supname)
        {
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT [Sup_ID]  FROM [Tbl_Supplier] where [Sup_Name]='" + supname + "'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    proid =(int) rdR["Sup_ID"];
                    rdR.Close();
                   
                }
                rdR.Close();
            }
            catch
            {
            }

            return proid;

        }

        public static int getcode(string stockid)
        {
            SqlDataReader rdR;
            try
            {
                DateTime date = System.DateTime.Now;
                String connStr = ConfigurationManager.ConnectionStrings["BestlifeDBConnectionString"].ConnectionString;
                Bestlife.ERP.DataAccess.Connection.connect(connStr);//connection string pass to connection class


                String Ssql = "SELECT[Code],[Pro_ID] FROM [Tbl_Stock] where [Sto_ID]='"+stockid+"'";

                rdR = Connection.ReadTable(Ssql);

                if (rdR.Read())
                {
                    Code =int.Parse(rdR["Code"].ToString());
                    rdR.Close();

                }
                rdR.Close();
            }
            catch
            {
            }

            return Code;

        }


       
    }
}



    
 
   
    
   
